
ALTER     function fn_return_previous_lot_info(@lot_id  int,@process_step varchar(20))
returns @fn_results Table( Lot_ID int, Created_On datetime ,Input_Quantity int )

AS
begin
	declare @results Table(Lot_ID int,  FL_Created_On datetime ,FL_Input_Quantity int)
	declare @temp_lot_id int
	declare @temp_created_on datetime
	declare @temp_qty int
	declare @table_name varchar (20)
	declare @count int 
	set @count = 0
	--set @table_name = @query_temp_table

	if @process_step = 'fab'
	begin
		declare createdCursor cursor for
		select Lot_ID,  FL_Created_On,FL_Input_Quantity from fab_lot_history where lot_id = @lot_id order by FL_Created_On desc
			open createdCursor
			fetch next from createdCursor into @temp_lot_id, @temp_created_on,@temp_qty
			while  @@fetch_status = 0
			begin	
				insert @results values(@temp_lot_id, @temp_created_on,@temp_qty)
				break
			end
 			close createdCursor
			deallocate createdCursor
	end
	else if @process_step = 'sort'
	begin
		declare createdCursor cursor for
		select Lot_ID,  Timestamp,SL_wafer_Qty from sort_lot_history where lot_id = @lot_id order by sort_lot_history.Timestamp desc
			open createdCursor
			fetch next from createdCursor into @temp_lot_id, @temp_created_on,@temp_qty
			while  @@fetch_status = 0
			begin	
				insert @results values(@temp_lot_id, @temp_created_on,@temp_qty)
				break
			end
	 		close createdCursor
			deallocate createdCursor
	end
	else if @process_step = 'assembly'
	begin
		declare createdCursor cursor for
		select Lot_ID,  Timestamp, AL_Qty_In from assembly_lot_history where lot_id = @lot_id order by assembly_lot_history.Timestamp desc
			open createdCursor
			fetch next from createdCursor into @temp_lot_id, @temp_created_on,@temp_qty
			while  @@fetch_status = 0
			begin	
				insert @results values(@temp_lot_id, @temp_created_on,@temp_qty)
				break
			end
 			close createdCursor
			deallocate createdCursor
	end
	else if @process_step = 'test'
	begin
		declare createdCursor cursor for
		select Lot_ID,  Timestamp, FTL_Qty_In from final_test_lot_history where lot_id = @lot_id order by final_test_lot_history.Timestamp desc
			open createdCursor
			fetch next from createdCursor into @temp_lot_id, @temp_created_on,@temp_qty
			while  @@fetch_status = 0
			begin	
				insert @results values(@temp_lot_id, @temp_created_on,@temp_qty)
				break
			end
 		close createdCursor
		deallocate createdCursor
	end
	else if @process_step = 'inventory'
	begin
		declare createdCursor cursor for
		select Lot_ID,  Timestamp, IL_Qty_In from inventory_lot_history where lot_id = @lot_id order by inventory_lot_history.Timestamp desc
			open createdCursor
			fetch next from createdCursor into @temp_lot_id, @temp_created_on,@temp_qty
			while  @@fetch_status = 0
			begin	
				insert @results values(@temp_lot_id, @temp_created_on,@temp_qty)
				break
			end
	 		close createdCursor
			deallocate createdCursor
	end
	else if @process_step = 'wafer store'
 	begin

 		declare createdCursor cursor for
 		select Lot_ID,  Timestamp, IL_Wafer_Qty_In from inventory_lot_history where lot_id = @lot_id order by inventory_lot_history.Timestamp desc
 			open createdCursor
 			fetch next from createdCursor into @temp_lot_id, @temp_created_on,@temp_qty
 			while  @@fetch_status = 0
 			begin	
 				insert @results values(@temp_lot_id, @temp_created_on,@temp_qty)
 				break
 			end
 	 		close createdCursor
 			deallocate createdCursor
 	end
		
		insert @fn_results select *from @results
	return
end







